﻿Imports System.Data.SqlClient
Public Class frmTeachers
    Dim CN As New SqlConnection
    Private Sub TEACHERBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TEACHERBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.TEACHERBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.TeachersDataSet)
    End Sub

    Private Sub frmTeachers_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'TeachersDataSet.TEACHER' table. You can move, or remove it, as needed.
        CN.ConnectionString = My.Settings.IT308ConnectionString
        Me.TEACHERTableAdapter.Fill(Me.TeachersDataSet.TEACHER)
    End Sub

    Private Sub Search_By_LastNameToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search_By_LastNameToolStripButton.Click
        Try
            Me.TEACHERTableAdapter.Search_By_LastName(Me.TeachersDataSet.TEACHER, LNameToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub Search_By_DepartmentToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search_By_DepartmentToolStripButton.Click
        Try
            Me.TEACHERTableAdapter.Search_By_Department(Me.TeachersDataSet.TEACHER, DepartmentToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub IDTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IDTextBox.TextChanged
        Load_Availability()
    End Sub
    Private Sub Load_Availability()
        'WB - Creates the connection to the Database to pull the students availability
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT ClassTime FROM TEACHER_AVAIL WHERE ID = @ID"
        cmd.Parameters.AddWithValue("@ID", IDTextBox.Text.Trim)
        Dim Reader As SqlDataReader
        Dim avail(15) As Char
        Dim i As Integer = 0
        Reader = Nothing
        chkMMorning.Checked = False
        chkMAfternoon.Checked = False
        chkMEvening.Checked = False
        chkTMorning.Checked = False
        chkTAfternoon.Checked = False
        chkTEvening.Checked = False
        chkWMorning.Checked = False
        chkWAfternoon.Checked = False
        chkWEvening.Checked = False
        chkRMorning.Checked = False
        chkRAfternoon.Checked = False
        chkREvening.Checked = False
        chkFMorning.Checked = False
        chkFAfternoon.Checked = False
        chkFEvening.Checked = False

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try

        Try
            'WB - Reads the availability and checks the appropriate boxes
            Reader = cmd.ExecuteReader
            While Reader.Read
                avail(i) = (Convert.ToChar(Reader.Item("ClassTime")))
                i += 1
            End While
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        End Try
        'WB - Closes connection
        Reader.Close()
        If CN.State = ConnectionState.Open Then CN.Close()

        For i = 0 To 14
            Select Case avail(i)
                Case Is = CChar("a")
                    chkMMorning.Checked = True
                Case Is = CChar("b")
                    chkMAfternoon.Checked = True
                Case Is = CChar("c")
                    chkMEvening.Checked = True
                Case Is = CChar("d")
                    chkTMorning.Checked = True
                Case Is = CChar("e")
                    chkTAfternoon.Checked = True
                Case Is = CChar("f")
                    chkTEvening.Checked = True
                Case Is = CChar("g")
                    chkWMorning.Checked = True
                Case Is = CChar("h")
                    chkWAfternoon.Checked = True
                Case Is = CChar("i")
                    chkWEvening.Checked = True
                Case Is = CChar("j")
                    chkRMorning.Checked = True
                Case Is = CChar("k")
                    chkRAfternoon.Checked = True
                Case Is = CChar("l")
                    chkREvening.Checked = True
                Case Is = CChar("m")
                    chkFMorning.Checked = True
                Case Is = CChar("n")
                    chkFAfternoon.Checked = True
                Case Is = CChar("o")
                    chkFEvening.Checked = True
            End Select
        Next
    End Sub
    Private Sub Add_Availability(ByRef avail As Char)
        'WB - Creates the connection to the Database to pull the students availability
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "INSERT  INTO TEACHER_Avail (ID, ClassTime) Values (@ID, @ClassTime)"
        cmd.Parameters.AddWithValue("@ID", IDTextBox.Text.Trim)
        cmd.Parameters.AddWithValue("@ClassTime", avail)

        'WB - Tries to open the connection
        Try
            CN.Open()
            'WB - Executes insert and if nothing is inserted throws an exception
            If cmd.ExecuteNonQuery() = 0 Then
                Throw New ApplicationException("Could Not Wright to Database")
            End If
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        Finally
            'WB - Closes the connection if it is still open.
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
    Private Sub Remove_Availability(ByRef avail As Char)
        'WB - Creates the connection to the Database to pull the students availability
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "DELETE  TEACHER_Avail WHERE ID = @ID AND ClassTime = @ClassTime"
        cmd.Parameters.AddWithValue("@ID", IDTextBox.Text.Trim)
        cmd.Parameters.AddWithValue("@ClassTime", avail)

        'WB - Tries to open the connection
        Try
            CN.Open()
            'WB - Executes insert and if nothing is inserted throws an exception
            If cmd.ExecuteNonQuery() = 0 Then
                Throw New ApplicationException("Could Not Delete from Database")
            End If
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
        Finally
            'WB - Closes the connection if it is still open.
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
    Private Sub chkMMorning_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkMMorning.Click
        If chkMMorning.Checked = True Then
            Add_Availability(CChar("a"))
        Else
            Remove_Availability(CChar("a"))
        End If
    End Sub

    Private Sub chkMAfternoon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkMAfternoon.Click
        If chkMAfternoon.Checked = True Then
            Add_Availability(CChar("b"))
        Else
            Remove_Availability(CChar("b"))
        End If
    End Sub

    Private Sub chkMEvening_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkMEvening.Click
        If chkMEvening.Checked = True Then
            Add_Availability(CChar("c"))
        Else
            Remove_Availability(CChar("c"))
        End If
    End Sub

    Private Sub chkTMorning_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkTMorning.Click
        If chkTMorning.Checked = True Then
            Add_Availability(CChar("d"))
        Else
            Remove_Availability(CChar("d"))
        End If
    End Sub

    Private Sub chkTAfternoon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkTAfternoon.Click
        If chkTAfternoon.Checked = True Then
            Add_Availability(CChar("e"))
        Else
            Remove_Availability(CChar("e"))
        End If
    End Sub

    Private Sub chkTEvening_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkTEvening.Click
        If chkTEvening.Checked = True Then
            Add_Availability(CChar("f"))
        Else
            Remove_Availability(CChar("f"))
        End If
    End Sub

    Private Sub chkWMorning_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkWMorning.Click
        If chkWMorning.Checked = True Then
            Add_Availability(CChar("g"))
        Else
            Remove_Availability(CChar("g"))
        End If
    End Sub

    Private Sub chkWAfternoon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkWAfternoon.Click
        If chkWAfternoon.Checked = True Then
            Add_Availability(CChar("h"))
        Else
            Remove_Availability(CChar("h"))
        End If
    End Sub

    Private Sub chkWEvening_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkWEvening.Click
        If chkWEvening.Checked = True Then
            Add_Availability(CChar("i"))
        Else
            Remove_Availability(CChar("i"))
        End If
    End Sub

    Private Sub chkRMorning_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkRMorning.Click
        If chkRMorning.Checked = True Then
            Add_Availability(CChar("j"))
        Else
            Remove_Availability(CChar("j"))
        End If
    End Sub

    Private Sub chkRAfternoon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkRAfternoon.Click
        If chkRAfternoon.Checked = True Then
            Add_Availability(CChar("k"))
        Else
            Remove_Availability(CChar("k"))
        End If
    End Sub

    Private Sub chkREvening_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkREvening.Click
        If chkREvening.Checked = True Then
            Add_Availability(CChar("l"))
        Else
            Remove_Availability(CChar("l"))
        End If
    End Sub

    Private Sub chkFMorning_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkFMorning.Click
        If chkFMorning.Checked = True Then
            Add_Availability(CChar("m"))
        Else
            Remove_Availability(CChar("m"))
        End If
    End Sub

    Private Sub chkFAfternoon_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkFAfternoon.Click
        If chkFAfternoon.Checked = True Then
            Add_Availability(CChar("n"))
        Else
            Remove_Availability(CChar("n"))
        End If
    End Sub

    Private Sub chkFEvening_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkFEvening.Click
        If chkFEvening.Checked = True Then
            Add_Availability(CChar("o"))
        Else
            Remove_Availability(CChar("o"))
        End If
    End Sub

    Private Sub btnChangePassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChangePassword.Click
        'WB - Creates the connection to the Database
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "Update Teacher set Password = @Password where ID = @StudentID"
        cmd.Parameters.AddWithValue("@StudentID", IDTextBox.Text)

        'WB - gets the new password from the change password form
        If frmChangePassword.ShowDialog() = Windows.Forms.DialogResult.OK Then
            If frmChangePassword.txtNew.Text = frmChangePassword.txtConfirm.Text Then
                cmd.Parameters.AddWithValue("@Password", frmChangePassword.txtNew.Text)
            Else
                Exit Sub
            End If
        Else
            Exit Sub
        End If
        'WB -  opens the connection and reports error if it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Cannot Connect to Database", "Error")
        End Try
        'WB - updates the record and reports error if it fails.
        Try
            If cmd.ExecuteNonQuery() = 0 Then
                Throw New Exception("No Records were Updated")
            End If
        Catch ex As Exception
            MessageBox.Show("Could not update the Database - " & ex.Message, "Error")
        Finally
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
End Class